using Dapper;
using Microsoft.Data.SqlClient;
using Lzfy_His_Service.Models;
using Lzfy_His_Service.Services;
using Lzfy_His_Service.Utilities;
using System.Text;
using Microsoft.Extensions.Logging;

namespace Lzfy_His_Service.Repositories
{
    public class UserRepository : IUserRepository
    {
        private readonly IDbConnectionFactory _connectionFactory;
        private readonly ILogger<UserRepository> _logger;

        public UserRepository(
            IDbConnectionFactory connectionFactory,
            ILogger<UserRepository> logger)
        {
            _connectionFactory = connectionFactory;
            _logger = logger;
        }

        public async Task<User?> GetByUsernameAsync(string username)
    {
        var stopwatch = System.Diagnostics.Stopwatch.StartNew();
        try
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = @"SELECT 
                id as Id,
                username as Username,
                password_hash as PasswordHash,
                email as Email,
                full_name as FullName,
                role as Role,
                is_active as IsActive,
                created_at as CreatedAt,
                updated_at as UpdatedAt,
                last_login_at as LastLoginAt
                FROM users WHERE username = @Username AND is_active = 1";
            var result = await connection.QueryFirstOrDefaultAsync<User>(sql, new { Username = username });
            
            DatabaseExceptionLogger.LogDatabasePerformance(_logger, "GetUserByUsername", stopwatch.Elapsed);
            return result;
        }
        catch (Exception ex)
        {
            DatabaseExceptionLogger.LogDatabaseException(_logger, ex, "GetUserByUsername", new { username });
            throw;
        }
    }

        public async Task<User?> GetByIdAsync(int id)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = "SELECT * FROM users WHERE id = @Id";
            return await connection.QueryFirstOrDefaultAsync<User>(sql, new { Id = id });
        }

        public async Task<IEnumerable<User>> GetAllAsync()
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = "SELECT * FROM users ORDER BY created_at DESC";
            return await connection.QueryAsync<User>(sql);
        }

        public async Task<int> CreateAsync(User user)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = @"
                INSERT INTO users (username, password_hash, email, full_name, role, is_active, created_at, updated_at)
                VALUES (@Username, @PasswordHash, @Email, @FullName, @Role, @IsActive, @CreatedAt, @UpdatedAt);
                SELECT CAST(SCOPE_IDENTITY() as int);";
            
            return await connection.QuerySingleAsync<int>(sql, user);
        }

        public async Task<bool> UpdateAsync(User user)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = @"
                UPDATE users 
                SET username = @Username, 
                    password_hash = @PasswordHash, 
                    email = @Email, 
                    full_name = @FullName, 
                    role = @Role, 
                    is_active = @IsActive, 
                    updated_at = @UpdatedAt
                WHERE id = @Id";
            
            var rowsAffected = await connection.ExecuteAsync(sql, user);
            return rowsAffected > 0;
        }

        public async Task<bool> DeleteAsync(int id)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = "UPDATE users SET is_active = 0, updated_at = GETDATE() WHERE id = @Id";
            var rowsAffected = await connection.ExecuteAsync(sql, new { Id = id });
            return rowsAffected > 0;
        }

        public async Task<bool> UpdateLastLoginAsync(int userId)
        {
            using var connection = _connectionFactory.CreateConnection();
            var sql = "UPDATE users SET last_login_at = GETDATE() WHERE id = @UserId";
            var rowsAffected = await connection.ExecuteAsync(sql, new { UserId = userId });
            return rowsAffected > 0;
        }

        // 新增权限管理方法实现
        public async Task<UserListResponse> GetUserListAsync(UserListRequest request)
    {
        var stopwatch = System.Diagnostics.Stopwatch.StartNew();
        try
        {
            using var connection = _connectionFactory.CreateConnection();
            
            var whereConditions = new List<string>();
            var parameters = new DynamicParameters();
            
            if (!string.IsNullOrEmpty(request.Username))
            {
                whereConditions.Add("u.username LIKE @Username");
                parameters.Add("@Username", $"%{request.Username}%");
            }
            
            if (!string.IsNullOrEmpty(request.FullName))
            {
                whereConditions.Add("u.full_name LIKE @FullName");
                parameters.Add("@FullName", $"%{request.FullName}%");
            }
            
            if (request.IsActive.HasValue)
            {
                whereConditions.Add("u.is_active = @IsActive");
                parameters.Add("@IsActive", request.IsActive.Value);
            }
            
            var whereClause = whereConditions.Count > 0 ? "WHERE " + string.Join(" AND ", whereConditions) : "";
            
            // 获取总数
            var countSql = $"SELECT COUNT(*) FROM users u {whereClause}";
            var total = await connection.QuerySingleAsync<int>(countSql, parameters);
            
            // 获取分页数据
            var offset = (request.Page - 1) * request.PageSize;
            var dataSql = $@"
                SELECT u.id as Id, u.username as Username, u.email as Email, 
                       u.full_name as FullName, u.is_active as IsActive, 
                       u.created_at as CreatedAt, u.last_login_at as LastLoginAt,
                       STRING_AGG(r.role_name, ',') as RoleNames
                FROM users u 
                LEFT JOIN sys_user_roles ur ON u.id = ur.user_id
                LEFT JOIN sys_roles r ON ur.role_id = r.id AND r.is_active = 1
                {whereClause}
                GROUP BY u.id, u.username, u.email, u.full_name, u.is_active, u.created_at, u.last_login_at
                ORDER BY u.created_at DESC
                OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";
            
            parameters.Add("@Offset", offset);
            parameters.Add("@PageSize", request.PageSize);
            
            var users = await connection.QueryAsync<UserDto>(dataSql, parameters);
            
            var result = new UserListResponse
            {
                Users = users.ToList(),
                Total = total,
                Page = request.Page,
                PageSize = request.PageSize
            };
            
            DatabaseExceptionLogger.LogDatabasePerformance(_logger, "GetUserList", stopwatch.Elapsed, result.Users.Count);
            return result;
        }
        catch (Exception ex)
        {
            DatabaseExceptionLogger.LogDatabaseException(_logger, ex, "GetUserList", new { request });
            throw;
        }
    }

        public async Task<User?> GetUserByIdAsync(int id)
        {
            return await GetByIdAsync(id);
        }

        public async Task<User?> GetUserByUsernameAsync(string username)
        {
            return await GetByUsernameAsync(username);
        }

        public async Task<int> AddUserAsync(User user)
        {
            return await CreateAsync(user);
        }

        public async Task<bool> UpdateUserAsync(User user)
        {
            return await UpdateAsync(user);
        }

        public async Task<bool> DeleteUserAsync(int id)
    {
        var stopwatch = System.Diagnostics.Stopwatch.StartNew();
        try
        {
            using var connection = _connectionFactory.CreateConnection();
            
            // 先删除用户角色关联
            await RemoveUserRolesAsync(id);
            
            // 然后删除用户
            var sql = "DELETE FROM users WHERE id = @Id";
            var rowsAffected = await connection.ExecuteAsync(sql, new { Id = id });
            
            DatabaseExceptionLogger.LogDatabasePerformance(_logger, "DeleteUser", stopwatch.Elapsed, rowsAffected);
            return rowsAffected > 0;
        }
        catch (Exception ex)
        {
            DatabaseExceptionLogger.LogDatabaseException(_logger, ex, "DeleteUser", new { id });
            throw;
        }
    }

        public async Task<bool> ToggleUserStatusAsync(int id, bool isActive)
        {
            using var connection = _connectionFactory.CreateConnection();
            
            var sql = "UPDATE users SET is_active = @IsActive, updated_at = GETDATE() WHERE id = @Id";
            var rowsAffected = await connection.ExecuteAsync(sql, new { Id = id, IsActive = isActive });
            return rowsAffected > 0;
        }

        public async Task<bool> ResetPasswordAsync(int id, string newPassword)
        {
            using var connection = _connectionFactory.CreateConnection();
            
            var sql = "UPDATE users SET password_hash = @PasswordHash, updated_at = GETDATE() WHERE id = @Id";
            var rowsAffected = await connection.ExecuteAsync(sql, new { Id = id, PasswordHash = newPassword });
            return rowsAffected > 0;
        }

        public async Task<bool> UsernameExistsAsync(string username, int? excludeId = null)
        {
            using var connection = _connectionFactory.CreateConnection();
            
            var sql = "SELECT COUNT(*) FROM users WHERE username = @Username";
            var parameters = new DynamicParameters();
            parameters.Add("@Username", username);
            
            if (excludeId.HasValue)
            {
                sql += " AND id != @ExcludeId";
                parameters.Add("@ExcludeId", excludeId.Value);
            }
            
            var count = await connection.QuerySingleAsync<int>(sql, parameters);
            return count > 0;
        }

        public async Task<List<UserRoleDto>> GetUserRolesAsync(int userId)
        {
            using var connection = _connectionFactory.CreateConnection();
            
            var sql = @"
                SELECT ur.user_id as UserId, ur.role_id as RoleId, 
                       r.role_name as RoleName, r.role_code as RoleCode
                FROM sys_user_roles ur
                INNER JOIN sys_roles r ON ur.role_id = r.id
                WHERE ur.user_id = @UserId AND r.is_active = 1";
            
            var roles = await connection.QueryAsync<UserRoleDto>(sql, new { UserId = userId });
            return roles.ToList();
        }

        public async Task<bool> AssignUserRolesAsync(int userId, List<int> roleIds)
    {
        var stopwatch = System.Diagnostics.Stopwatch.StartNew();
        using var connection = _connectionFactory.CreateConnection();
        using var transaction = connection.BeginTransaction();
        
        try
        {
            // 先删除现有角色
            await connection.ExecuteAsync(
                "DELETE FROM sys_user_roles WHERE user_id = @UserId", 
                new { UserId = userId }, transaction);
            
            // 添加新角色
            if (roleIds.Any())
            {
                var insertSql = "INSERT INTO sys_user_roles (user_id, role_id) VALUES (@UserId, @RoleId)";
                foreach (var roleId in roleIds)
                {
                    await connection.ExecuteAsync(insertSql, 
                        new { UserId = userId, RoleId = roleId }, transaction);
                }
            }
            
            transaction.Commit();
            
            DatabaseExceptionLogger.LogDatabasePerformance(_logger, "AssignUserRoles", stopwatch.Elapsed, roleIds.Count);
            return true;
        }
        catch (Exception ex)
        {
            transaction.Rollback();
            DatabaseExceptionLogger.LogDatabaseException(_logger, ex, "AssignUserRoles", new { userId, roleIds });
            return false;
        }
    }

        public async Task<bool> RemoveUserRolesAsync(int userId)
        {
            using var connection = _connectionFactory.CreateConnection();
            
            var sql = "DELETE FROM sys_user_roles WHERE user_id = @UserId";
            var rowsAffected = await connection.ExecuteAsync(sql, new { UserId = userId });
            return rowsAffected >= 0; // 即使没有角色也算成功
        }
    }
}